package com.atguigu3;

import com.utils.JDBCUtils;
import org.junit.Test;

import java.io.InputStream;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Properties;

/**
 * @auther user
 * @date 2021/12/6 - 14:28
 */
public class PreparedStatementUpdateTest {
   
    //向customers表中插入一条数据
    @Test
    public void testInsert() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            //1 读取配置信息
            InputStream inputStream = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
            Properties properties = new Properties();
            properties.load(inputStream);

            String user = properties.getProperty("user");
            String password = properties.getProperty("password");
            String url = properties.getProperty("url");
            String driverClass = properties.getProperty("driverClass");

            //2 加载驱动
            Class.forName(driverClass);
            //3 获取连接
            connection = DriverManager.getConnection(url, user, password);
            //4 预编译sql,返回preparedStatement
            String sql = "insert into customers(name,email,birth) values(?,?,?)";
            preparedStatement = connection.prepareStatement(sql);
            //5 填充占位符
            preparedStatement.setString(1,"哪吒");
            preparedStatement.setString(2,"nezha@qq.com");
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
            java.util.Date date = simpleDateFormat.parse("2000-1-12");
            preparedStatement.setDate(3,new Date(date.getTime()));
            //6 执行操作
            preparedStatement.execute();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //7 关闭资源
            try {
                if (preparedStatement != null){
                    preparedStatement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (connection != null){
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }
    //修改表中的一条数据
    @Test
    public void testUpdate() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            //1 加载配置文件
            InputStream inputStream = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
            Properties properties = new Properties();
            properties.load(inputStream);
            String user = properties.getProperty("user");
            String password = properties.getProperty("password");
            String url = properties.getProperty("url");
            String driverClass = properties.getProperty("driverClass");
            //2 加载驱动
            Class.forName(driverClass);
            //3 获取连接
            connection = DriverManager.getConnection(url, user, password);
            //4 预编译sql,返回PreparedStatement实例
            String sql = "update customers set name = ? where id = ?";
            preparedStatement = connection.prepareStatement(sql);
            //5 填充占位符
            preparedStatement.setString(1,"奶子");
            preparedStatement.setInt(2,25);
            //6 执行
            preparedStatement.execute();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //7 关闭资源
            try {
                if (preparedStatement != null){
                    preparedStatement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (connection != null){
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    //通用增删改查
    public void update(String sql,Object ...args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            //1 获取数据库连接
            connection = JDBCUtils.getConnection();
            //2 预编译sql语句
            preparedStatement = connection.prepareStatement(sql);
            //3 填充占位符
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i+1,args[i]);
            }
            //4 执行
            preparedStatement.execute();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //5 关闭资源
            JDBCUtils.closeResource(connection,preparedStatement);
        }
    }
    @Test
    public void testupdate() throws Exception{
        String sql = "insert into customers(name,email,birth) values(?,?,?)";
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        java.util.Date parse = simpleDateFormat.parse("2021-12-09");
        Date date = new Date(parse.getTime());

        update(sql,"张三","zhansan@qq.com",date);
    }
}
